# coding: utf-8

import time
import arrow
import pymysql
import copy
import re
import datetime
from decimal import Decimal

from .variable import VAR
from .logger import logger

__all__ = ["sql"]

"""
#---定期----
SET @pno = 'PD201808151443326832720'; # pno参数
SET @pid = (SELECT id FROM finance_fixin_product.t_fixin_periodic_product WHERE pno=@pno); #pid参数
SET @tmpId = (SELECT tmp_id FROM finance_fixin_product.t_fixin_periodic_product WHERE pno=@pno); #tmp_id参数

#---计划类----
SET @pno = 'ZC201808151429077458882';
SET @pid = (SELECT id FROM finance_fixin_product.t_fixin_zc_product WHERE pno = @pno);
SET @tmp_id = (SELECT tmp_id FROM finance_fixin_product.t_fixin_zc_product WHERE pno = @pno);
SELECT @pid;

"""
HOST = {
    "Test1": "10.201.5.10",
    "Test2": "10.201.5.9",
    "Test3": "10.201.5.8",
    "Test4": "10.201.5.7",
    "Test5": "10.201.5.6",
    "Test6": "10.201.5.5",
}

MYSQL = {
    "USER": "JR_modify",
    "PASSWORD": "$q51jrb@ZYRTTh6U",
    "PORT": 3306,
}


class P:
    def __init__(self, data_list):
        self.data_list = data_list
        self.init()
        self.init_fmt()

    def init(self):
        self.data_list = [list(i) for i in self.data_list]

        for k, item in enumerate(self.data_list):
            for i, v in enumerate(item):
                if isinstance(v, Decimal):
                    self.data_list[k][i] = str(v)
                elif isinstance(v, datetime.datetime):
                    self.data_list[k][i] = v.strftime("%Y-%m-%d %H:%M:%S")
                elif isinstance(v, datetime.date):
                    self.data_list[k][i] = str(v)
                elif not v:
                    self.data_list[k][i] = ""

    def get_len(self, val):
        "获取长度，一个中文算2个长度"
        val = str(val)
        length = len(val)
        utf8_length = len(val.encode('utf-8'))
        length = (utf8_length - length) / 2 + length
        return int(length)

    def init_fmt(self):
        """初始化fmt"""
        offset = 2

        self.index_length = {}  # 记录每一个position的最大的长度,及最长的那个字符串
        for i, key in enumerate(self.data_list[0]):

            max_length_str = ''
            length = 0
            for item in self.data_list:
                _len = self.get_len(item[i])
                if length < _len:
                    length = _len
                    max_length_str = str(item[i])

            # fmt += "| {:<#}".replace("#", str(length+offset))
            self.index_length[i] = (i, length + offset, max_length_str)

    def get_fmt(self, str_list):
        # 中文越少越宽，取最大长度，中文少一个加1，中文多一个减1
        fmt = ''
        for i, v in enumerate(str_list):
            _, length, max_length_str = self.index_length[i]
            l1 = re.findall(r"[\u4e00-\u9fa5]", max_length_str)  # 获取最长的字符串中的中文个数
            if v:
                l2 = re.findall(r"[\u4e00-\u9fa5]", str(v))  # 获取当前的字符串中的中文个数
            else:
                l2 = []
            res = len(l1) - len(l2)
            fmt += "| {:<#}".replace("#", str(length + res))

        fmt += "| "
        # print(fmt)
        return fmt

    @classmethod
    def print_list(cls, res):
        cls(res).print_res()

    def print_res(self):
        l = []
        for i in self.data_list:
            fmt = self.get_fmt(i)
            l.append(fmt.format(*i) + "\n")

        max_len = max(self.get_len(i) for i in l)

        # print("+ {} +".format("-"*(max_len-6)))
        # print(l[0],end="")
        # print("+ {} +".format("-" * (max_len - 6)))
        # print("".join(l[1:]),end="")
        # print("+ {} +".format("-" * (max_len - 6)))

        str_list = []
        str_list.append("\n")

        str_list.append("+ {} +".format("-" * (max_len - 6)))
        str_list.append("\n")

        str_list.append(l[0])

        str_list.append("+ {} +".format("-" * (max_len - 6)))
        str_list.append("\n")

        str_list.append("".join(l[1:]))

        str_list.append("+ {} +".format("-" * (max_len - 6)))
        str_list.append("\n")

        logger.info("".join(str_list))


class Sql(object):
    def __init__(self, conn):
        self.conn = conn
        self.cursor = self.conn.cursor()
        self.p = P

    @classmethod
    def connect(cls, env):
        if env not in HOST:
            raise Exception("请指定正确的Test环境：test1~test6")

        connection = None

        try:
            connection = pymysql.connect(
                host=HOST[env],
                port=MYSQL['PORT'],
                user=MYSQL['USER'],
                password=MYSQL['PASSWORD'],
                charset='utf8'
            )
        except pymysql.err.OperationalError as e:
            logger.error(e)
            if 'Errno 10060' in str(e) or '2003' in str(e):
                logger.error('数据库连接失败，请检查VPN是否开启~')
        except pymysql.err.InternalError as e:
            logger.error('数据库连接失败 Error:' + str(e))

        return cls(connection)

    def exec(self, sql,*args):
        """执行sql语句"""
        logger.info("执行sql：{}".format(sql))
        try:
            self.cursor.execute(sql,args)
            self.conn.commit()
            res = self.cursor.fetchall()

            res = self.printf(res)

            return res
        except Exception as e:
            logger.exception(e)

    def insert(self, table_name, **kwargs):

        keys, vals = tuple(kwargs.keys()), tuple(kwargs.values())

        cols = ",".join(keys)

        wildcards = ",".join(["%s" for i in range(len(vals))])
        sql = 'insert into {}({}) VALUES ({});'.format(table_name, cols, wildcards)

        logger.info("执行sql：{}".format(sql))

        self.cursor.execute(sql, args=vals)
        self.conn.commit()

        res = self.cursor.fetchall()

        self.printf(res)

    def update(self, table_name, where=None, **kwargs):
        """
        :param table_name: 
        :param cols: 
        :param where: 
        :return: 
        """
        filter_condition = ""  # 筛选条件
        vals_condition = tuple()
        if where:
            vals_condition = tuple(where.values())
            for k, v in where.items():
                filter_condition += "where {}=%s".format(k)

        keys, vals = tuple(kwargs.keys()), tuple(kwargs.values())
        cols = ",".join(keys)
        wildcards = ",".join(["{}=%s".format(keys[i]) for i in range(len(vals))])

        sql = 'UPDATE {} SET {} {};'.format(table_name, wildcards, filter_condition)

        # 将筛选条件的val加到tuple
        vals = vals + vals_condition
        self.cursor.execute(sql, args=vals)

        logger.info("执行sql：{}   vals：{}".format(sql,vals))
        self.conn.commit()

        res = self.cursor.fetchall()

        self.printf(res)

    # def printf(self, res):
    #
    #     if not self.cursor.description:
    #         return
    #
    #     res = copy.deepcopy(res)
    #     res = list(res)
    #
    #     fields = []
    #     for field in self.cursor.description:
    #         fields.append(field[0])
    #     res.insert(0, fields)
    #
    #     # 获取表信息时可以打开
    #     # d = {}
    #     # for i in range(len(fields)):
    #     #     d[res[0][i]] = res[1][i]
    #     #
    #     # print(d)
    #
    #     P.print_list(res)

    def printf(self, res):

        if not self.cursor.description:
            return

        res = copy.deepcopy(res)
        res = list(res)

        fields = []
        for field in self.cursor.description:
            fields.append(field[0])
        res.insert(0, fields)

        # 获取表信息时可以打开
        # d = {}
        # for i in range(len(fields)):
        #     d[res[0][i]] = res[1][i]
        #
        # print(d)

        P.print_list(res)

        # 将res处理成，一个列表包含多个字典的形式
        res = [dict(zip(fields, item)) for item in res[1:]]
        return res

    def __del__(self):
        self.conn.close()


sql = Sql.connect(VAR.env)
